package com.report.reportserver.service.impl;

import com.report.reportserver.service.SysSqlService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ ClassName: SysSqlServiceImpl
 * @ Description：直接对数据库表进行定义
 * @ Modified By：
 * @ Version:
 * @ Author     ：LiYing
 * @ Date       ：Created in 14:21 2022/4/7
 */
@Service
public class SysSqlServiceImpl implements SysSqlService {
    @Autowired
    private EntityManager entityManager;

    @Override
    @Transactional
    @Modifying
    /*
     * 删除数据库表
     * @param tableName
     * @return boolean
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/7 22:45
     */
    public boolean dropDatabaseTable(String tableName) {
        String sql = "DROP TABLE IF EXISTS " + tableName;
        Query query = this.entityManager.createNativeQuery(sql);
        int ans = query.executeUpdate();
        return true;
    }

    @Override
    @Transactional
    @Modifying
    /*
     * 生成数据库表
     * @param sql
     * @return int
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/7 22:46
     */
    public int createDatabaseTable(String sql) {
        Query query = this.entityManager.createNativeQuery(sql);
        return query.executeUpdate();
    }

    @Override
    @Transactional
    @Modifying
    /*
     * 查询数据库某个表基本信息
     * @param tableName
     * @return java.util.Map
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/7 22:47
     */
    public Map getTableInfoMap(String tableName) {
        Query query;
        String sql;
        sql = "SELECT column_name, ordinal_position, data_type";
        sql += " FROM information_schema.COLUMNS ";
        sql += "  WHERE ( table_name = '" + tableName + "')";
        sql += "  order by ordinal_position";

        query = entityManager.createNativeQuery(sql);
        List list = query.getResultList();
        String name, type;
        Object a[];
        Map map = new HashMap();
        String keyId = null;
        if (list != null && list.size() > 0) {
            a = (Object[]) list.get(0);
            keyId = a[0].toString();
            map.put("keyId", keyId);
            map.put("columnList", list);
        }

        return map;
    }

    @Override
    @Transactional
    @Modifying
    /*
     * 根据一组列名来创建数据库表，默认属性为varchar，会额外附加id主键
     * @param cols
     * @return int
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/15 15:25
     */
    public int createDatabaseTableByColName(List<String> cols, String tableName) {
        //生成sql语句来建表，例:CREATE TABLE workman (id INT(11) PRIMARY KEY,name VARCHAR(255),phone VARCHAR(255),address VARCHAR(255))
        StringBuilder sqlBuilder = new StringBuilder("CREATE TABLE " + tableName + " (id INT(11) PRIMARY KEY AUTO_INCREMENT,");
        for (String col : cols) {
            sqlBuilder.append(col).append(" VARCHAR(255),");
        }
        String sql = sqlBuilder.toString().substring(0, sqlBuilder.length() - 1);
        sql += ")";

        Query query = this.entityManager.createNativeQuery(sql);
        return query.executeUpdate();
    }

    @Override
    @Transactional
    @Modifying
    /*
     * 根据列名，多条list数据，表名自动向数据库中插入数据
     * @param objects
     * @param cols
     * @param tableName
     * @return int
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/15 16:33
     */
    public int insertData(List<List<String>> objects, List<String> cols, String tableName) {

        //循环对每一条数据进行插入
        for (List<String> object : objects) {

            StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
            sqlBuilder.append(tableName).append(" (");
            for (String col : cols) {
                sqlBuilder.append(col).append(",");
            }
            sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);//去除多余的一个逗号
            sqlBuilder.append(") VALUES (");

            for (String data : object) {
                sqlBuilder.append("'").append(data).append("'").append(",");
            }
            sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);//去除多余的一个逗号
            sqlBuilder.append(");");

            //执行插入
            String sql = sqlBuilder.toString();

            Query query = this.entityManager.createNativeQuery(sql);
            query.executeUpdate();
        }
        return 0;
    }

    @Override
    @Transactional
    @Modifying
    /*
     * 解析插入sql直接插入一条自定义数据
     * @param sql
     * @return int
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/21 15:13
     */
    public boolean insertDataBySql(String sql) {
        Query query = this.entityManager.createNativeQuery(sql);
        try {
            query.executeUpdate();
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }

    @Override
    @Transactional
    @Modifying
    /*
     * 根据表名查询表中的所有数据
     * @param tableName
     * @return java.util.List
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/21 13:47
     */
    public List selectTableData(String tableName) {
        String sql = "select * from "+tableName;
        Query query = entityManager.createNativeQuery(sql);
        List<Object[]> list = query.getResultList();
//        System.out.println(list.size());
//        System.out.println(list.get(0)[0].toString());
        return list;
    }

    @Override
    public String getMaxNum(String tableName, String columnName, String prefix) {
        return null;
    }

    @Override
    public List findListByQuerySql(String[] sql, String key, List columns, List items, Map queryMap) {
        return null;
    }

    @Override
    public String getForeRecordLabel(String sql, Integer id) {
        return null;
    }

    @Override
    @Transactional
    @Modifying
    /*
     * 获得数据库中所有的表名
     * @param
     * @return java.util.List<java.lang.String>
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/20 16:27
     */
    public List getTableNames() {
        String sql = "select table_name from information_schema.tables where table_schema='reporting_form'";

        Query query = entityManager.createNativeQuery(sql);
        List list = query.getResultList();
        return list;
    }

    @Override
    @Transactional
    @Modifying
    /*
     * 解析没有明确规定的sql查询并返回对象
     * @param sql
     * @return java.util.List
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/10 17:43
     */
    public List getSelectList(String sql) {
        List rList = new ArrayList();
        Query query = this.entityManager.createNativeQuery(sql);
        List list = query.getResultList();
        /*if(list == null || list.size() == 0)
            return rList;
        Object a[];
        Map m;
        for(int i = 0;i<list.size();i++) {
            a = (Object[]) list.get(i);
            m = new HashMap();
            m.put("value", a[0]);
            m.put("label", a[1]);
            rList.add(m);
        }*/
        return list;
    }

    @Override
    public List getIdNameTreeSelectList(String sql, String parentkey) {
        return null;
    }

    @Override
    public void addChildrenDepartment(Integer parentId, Map map, String sql, String parentkey) {

    }

    @Override
    public void setSelectList(Map data, List items) {

    }

    @Override
    public List transArrayToMapList(String key, List columns, List list) {
        return null;
    }

    @Override
    public Map findPageByQuerySql(String[] sql, String key, List columns, List items, Map queryMap, Integer currentPage, Integer pageSize) {
        return null;
    }

    @Override
    public Map findPageByQuerySql(String[] sql, String key, List columns, List items, Map queryMap, Integer currentPage) {
        return null;
    }

    @Override
    public Map<String, Object> findMapByKey(String tableName, String key, Integer keyId) {
        return null;
    }

    @Override
    public Object findDataObjectBySql(String sql, Map paras) {
        return null;
    }

    @Override
    public Object findDataListBySql(String sql, Map paras) {
        return null;
    }

    @Override
    public String updateByMapKeyOld(String tableName, String key, Integer keyId, Map data) {
        return null;
    }

    @Override
    public String getNextNum(String tableName, String columnName, Map formObj) {
        return null;
    }

    @Override
    public String updateByMapKey(String tableName, String key, Integer keyId, Map data, Map formObj, String url) {
        return null;
    }

    @Override
    @Transactional
    @Modifying
    /*
     * 根据key更新某行的信息
     * @param tableName
     * @param columnName
     * @param key
     * @param keyId
     * @param value
     * @param url
     * @return java.lang.String
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/8 9:29
     */
    public String updateColumnValueByKey(String tableName, String columnName, String key, Integer keyId, Object value) {
        Query query;
        String sql = " update " + tableName + " set " + columnName + "=" + "'" + value + "'" + " where " + key + "=" + keyId;
        query = this.entityManager.createNativeQuery(sql);
        query.executeUpdate();
        return null;
    }

    @Override
    public int deleteByKey(String tableName, String key, Integer keyId, String url) {
        return 0;
    }
}
